(ns ca.uoit.molly.mycampus
 "Schema definitions for mycampus")

(def *db*
  {:classname "org.sqlite.JDBC"
   :subprotocol "sqlite"
   :subname "data/mycampus.sq3"})

;
; entity definitions
; :entity - name of the entity set
; :from - SQL FROM phrase.  Can be a JOIN of multiple tables
; :id - SQL column phrase.  Can have any scalar function expressions "as id"
; :attr - an array-map mapping attribute names to SQL phrases.
;
(def *entities*
  {:course {:entity "course"
            :from "courses"
            :id "code as id"
            :attr (array-map :code "code" :title "title" :desc "description")
            :values [:code :title :desc]}
   :schedule {:entity "schedule"
              :from "sections S JOIN schedules T on S.id = T.section_id"
              :id "T.id as id"
              :attr (array-map :date_start "date_start" :date_end "date_end"
                    :hour_start "hour_start" :min_start "min_start"
                    :hour_end "hour_end" :min_end "min_end"
                    :location "location"
                    :campus "campus")
              :values [:location :campus]}
  :person {:entity "person"
           :from "instructors"
           :id "id"
           :attr (array-map :name "name")
           :values [:name]}
   })

(def *course-schedule*
   {:project "C.code as code,
              E.id as section_id,
              E.campus as campus,
              E.semester as semester,
              S.id as schedule_id,
              S.date_start as date_start,
              S.date_end as date_end,
              S.schedtype as schedule_type,
              S.hour_start as hour_start,
              S.min_start as min_start,
              S.hour_end as hour_end,
              S.min_end as min_end,
              S.location as location,
              I.id as instructor_id,
              T.position as position"
    :from "courses C JOIN sections E ON c.code = E.course
               JOIN schedules S ON E.id = S.section_id
               JOIN teaches T ON T.schedule_id = S.id
               JOIN instructors I on T.instructor_id = I.id"
    :top-index [:code :semester]
    :hierarchy [{:entity "course" :id "code" :parameters ["semester"]}
                {:entity "section" :id "section_id"}
                {:entity "schedule" :id "schedule_id"}
                {:entity "instructor" :id "instructor_id" :parameters ["position"]}
                ]
    })

(def *entity-groups*
  {:course-schedule *course-schedule*
   })